<?php 
$req_path="../";
require_once($req_path."includes/ini.php");
require_once($req_path."includes/db.php");

class M_Student extends DB{
    private $rowCount=0;
    public $table_name="";
	   function M_Student(){
		parent::__construct();
    }
   function MonthThai($m){
     $thaimonth=array("มกราคม","กุมภาพันธ์","มีนาคม","เมษายน","พฤษภาคม","มิถุนายน","กรกฎาคม","สิงหาคม","กันยายน","ตุลาคม","พฤศจิกายน","ธันวาคม");
	  
	 return $thaimonth[$m];
	
   }
   
    function getAgeLevel(){
		$sql = " SELECT DATE_FORMAT(age_date,'%d') as DateAge ,  DATE_FORMAT(age_date,'%m') as DateMonth   FROM  tb_master_age order by id limit 0,1 " ;
		$result = $this->ExecuteData($sql);
		$row=$result->FetchRow();
		return $row['DateAge'] . " " . $this->MonthThai($row['DateMonth']-1);
	}
	
	function getClassLevel(){
	 
		$sqlscript= "select id, name from tb_class_level where status = 'Y' Order by id asc ";
		
		$result = $this->ExecuteData($sqlscript);
		while($row = $result->FetchRow()){
			$ar[$row["name"]] = $row["id"];
		}
		return $ar;	
	}
	function load_current_amt_room($cid){
		
		$sql = "SELECT  cr.class_id, cr.room,  
				 (
					SELECT count(*)
					FROM  tb_student_year st  
					LEFT JOIN tb_student s  on s.student_id = st.student_id and s.`status` = 3	
					WHERE st.class_id = cr.class_id  and s.delete_flag = 0 and st.room = cr.room and st.year = ". $this->getCurrentYear() ."
				 )
				 cnt , cr.amount
				FROM tb_class_room cr 
				WHERE cr.`status` = 'Y' and cr.class_id = ".$cid."
				group by cr.class_id, cr.room" ;
		
		$result = $this->ExecuteData($sql);
        $this->rowCount = $result->RecordCount();
        return $result;
	}
    function loadStudent_assign($cid=0){
	
        $sql = "SELECT sd.student_id, sd.`status` AS status_id, ss.name AS status_name
				, sd.remark, CASE WHEN info.sex ='M' THEN 'ด.ช. ' ELSE 'ด.ญ. ' END AS title_name, ifnull(info.thai_nickname,'') as thai_nickname 
				, info.thai_name, info.thai_surname, ifnull( info.birthdate,'' ) as birthdate, ifnull( DATE_FORMAT(info.birthdate, '%d/%m/%Y') ,'') birth_date, info.init_class 
				, (SELECT DATE_FORMAT(age_date,'%m/%d') FROM tb_master_age aa ORDER BY aa.id  desc limit 0,1) age_master
				,  DATE_FORMAT( ifnull(info.registerdate,''), '%d/%m/%Y')  register_date
				,   DATE_FORMAT( ifnull(info.createdate,''), '%d/%m/%Y')  create_date
				FROM tb_student sd
				JOIN tb_student_info info ON sd.student_id = info.student_id
				JOIN tb_status_student ss ON ss.id = sd.status
				WHERE sd.`status` = 1 and sd.delete_flag = 0 ";
		if ($cid!=0){
			$sql .= " AND info.init_class=".$cid;
		}		
		$sql.=" ORDER BY info.birthdate asc, info.id ASC  ";
		 
		//print_r($sql);
        $result = $this->ExecuteData($sql);
		
        $this->rowCount = $result->RecordCount();
        return $result;
    }
	function check_room_avaliable($cid, $room){
		$sql  = " SELECT  cr.class_id, cr.room , cr.amount
					FROM tb_class_room cr 
					WHERE cr.`status` = 'Y' and cr.class_id = ".$cid." and cr.room=".$room." 
					AND cr.amount >  (
						SELECT count(*)
						FROM  tb_student_year st  
						LEFT JOIN tb_student s  on s.student_id = st.student_id and s.`status` = 3	
						WHERE st.class_id = cr.class_id  and s.delete_flag = 0 and st.room = cr.room and st.year = ". $this->getCurrentYear() ."
					 ) 
					group by cr.class_id, cr.room" ;
		$result = $this->ExecuteData($sql);
        //$this->rowCount = $result->RecordCount();
        return  $result->RecordCount();
	
	}
	function updateStudentStatus($id,$status,$uid){
		$sql = " UPDATE tb_student SET status=".$status." , updatedate=now(), updateby=".$uid." WHERE student_id = ".$id;
		$this->ExecuteData($sql);
	}
	function AddStudentToRoom($val){
		$sqlInsert=$this->InsertSqlScript("SELECT year, student_id, class_id, room , createdate, createby FROM tb_student_year where 1=2 ", $val);
		$this->ExecuteData($sqlInsert);
	}
	//Show only room avaliable
	function load_room($cid){
		$sql=" SELECT cr.room as id, cr.room   as name
			FROM tb_class_level cl
			JOIN tb_class_room cr on cr.class_id = cl.id
			WHERE cl.id = ".$cid ;
		$sql .= " AND cr.amount > (SELECT count(*) 
								FROM  tb_student_year st  
								LEFT JOIN tb_student s  on s.student_id = st.student_id and s.`status` = 3	 and s.delete_flag = 0
								WHERE st.class_id = cr.class_id  and st.room = cr.room and st.year=".$this->getCurrentYear().")";
		$sql.=" order by cl.id, cr.room ";
		
		$result = $this->ExecuteData($sql);
		while($row = $result->FetchRow()){
			$ar[$row["name"]] = $row["id"];
		}
		return $ar;	
	}
	
	function load_fee_type(){
		$sql="  select id, term
				 from tb_fee_type
				 where id in (1,2,3,4,10) "; 
				 
		$result = $this->ExecuteData($sql);
		while($row = $result->FetchRow()){
			$ar[$row["id"]] = $row["term"];
		}
		return $ar;			 
	}
	//Show all room
	function load_all_room($cid){
		$sql="SELECT cr.room as id, cr.room   as name
			FROM tb_class_level cl
			JOIN tb_class_room cr on cr.class_id = cl.id
			WHERE cl.id = ".$cid ;
		$sql.=" order by cl.id, cr.room ";
		
		$result = $this->ExecuteData($sql);
		while($row = $result->FetchRow()){
			$ar[$row["name"]] = $row["id"];
		}
		return $ar;	
	}
	 
	function updateStatusExit($id,$status,$remark, $uid){
		$sql = " UPDATE tb_student SET status=".$status.", remark='".$remark."' , updatedate=now(), updateby=".$uid." WHERE student_id = ".$id;
		$this->ExecuteData($sql);
	}
	function updateRoom($id, $room ,$reason ,$uid){
		$sql = " UPDATE tb_student_year SET room=".$room." ,remark='".$reason."' , updatedate=now(), updateby=".$uid." WHERE student_id = ".$id ." and year=".$this->getCurrentYear();
		$this->ExecuteData($sql);
		
		$sql = " UPDATE tb_student SET   remark='".$reason."' , updatedate=now(), updateby=".$uid." WHERE student_id = ".$id;
		$this->ExecuteData($sql);
		
	}
	function student_AdjustLevel($val ){
		if (isset($val)){
			$sqlInsert=$this->InsertSqlScript("SELECT year, student_id, class_id, room, createdate, createby FROM tb_student_year where 1=2 ", $val);
			$this->ExecuteData($sqlInsert);
         }
	}
	function getStudentStatus(){
		
		$sqlscript= "SELECT id, name
					FROM tb_status_student
					WHERE 1=1 
					ORDER BY id asc";
					//and status='Y' and id IN (3,5,6)
       	$result = $this->ExecuteData($sqlscript);
		while($row = $result->FetchRow()){
			$ar[$row["name"]] = $row["id"];
		}
		return $ar;	
	}
	
	function loadStudentMove_Exit($c, $sname,$cid, $r, $s){
		$sql = "SELECT s.student_id,st.year, s.code, s.student_id , ss.name as status_name, st.class_id, st.room , s.remark , cl.alian as class_name
				 , CASE WHEN info.sex ='M' THEN 'ด.ช. ' ELSE 'ด.ญ. ' END AS title_name  
				 , info.thai_name, info.thai_surname , s.status, ifnull(info.thai_nickname,'') as thai_nickname
				 , DATE_FORMAT(s.updatedate,'%d/%m/%Y') updatedate
				FROM tb_student s 
				LEFT JOIN tb_student_info info on info.student_id = s.student_id
				JOIN tb_student_year st on st.student_id = s.student_id and st.year = ".$this->getCurrentYear()."
				JOIN tb_status_student ss on ss.id =s.`status`
				JOIN tb_class_level  cl on cl.id = st.class_id
				WHERE   s.delete_flag = 0 ";
		if ($c!=''){
			$sql .= " AND s.code ='".$c."'";
		}
		if (trim($sname) !=''){
			$sname=trim($sname);
			list($n,$s)= explode(" ",$sname);
			$sql .=" AND (info.thai_name like '%".$m."%'";
			$sql .=" and info.thai_surname like '%".$s."%')";
		}
		if ($cid!=0){
			$sql .=" AND st.class_id =".$cid;
			if ($r!=-1 || $r != null){
				$sql.=" AND st.room =".$r;
			}
		}
		
		if ($s!=0){
			$sql.=" AND  s.status = ".$s;
		}
		
		$sql .= " ORDER BY st.class_id,st.room, info.thai_name ";
	
	    $result = $this->ExecuteData($sql);
		
        $this->rowCount = $result->RecordCount();
        return $result;
	}
	/*function getCurrentTerm(){
		$sql = "SELECT name, value
				FROM tb_system_config  
				WHERE name IN ( 'CURR_YEAR' , 'CURR_TERM' ) ";
				
		$result = $this->ExecuteData($sql);	
		return $result;	
	}*/
	function loadStudent($c, $sname='',$cid=0, $r, $s=0,$year=2557){
		$sql = "SELECT s.student_id,st.year, s.code, s.student_id, ss.name as status_name, st.class_id, st.room , s.remark , cl.alian as class_name
				 , CASE WHEN info.sex ='M' THEN 'ด.ช. ' ELSE 'ด.ญ. ' END AS title_name  
				 , info.thai_name, info.thai_surname , s.status, ifnull(info.thai_nickname,'') as  thai_nickname
				 , congenital_disease, ifnull(food_allergies,'') as food_allergies, DATE_FORMAT(registerdate,'%d/%m/%Y') register_date
				 , DATE_FORMAT( ifnull(info.createdate,''), '%d/%m/%Y')  create_date
				FROM tb_student s 
				LEFT JOIN tb_student_info info on info.student_id = s.student_id
				JOIN tb_student_year st on st.student_id = s.student_id and st.year = ".$year."
				JOIN tb_status_student ss on ss.id =s.`status`
				JOIN tb_class_level  cl on cl.id = st.class_id
				WHERE 1=1 and s.delete_flag = 0 ";
		if ($c!=''){
			$sql .= " AND s.code ='".$c."'";
		}
		if ($sname!=''){	
			$sname=trim($sname);
			list($n,$s)= explode(" ",$sname);
			$sql .=" AND (info.thai_name like '%".$n."%'";
			$sql .=" AND info.thai_surname like '%".$s."%')";
		}
		if ($cid!=0){
			$sql .=" AND st.class_id =".$cid;
			if ($r!= -1 || $r != null){
			$sql.=" AND st.room =".$r;
			}
		}
	
		if ($s!=0){
			$sql.=" AND  s.status = ".$s;
		}
		
		$sql .= " ORDER BY st.class_id,st.room, s.code  ";
		

	    $result = $this->ExecuteData($sql);
		
        $this->rowCount = $result->RecordCount();
        return $result;
	}
	function loadInvoice($c, $sname,$cid, $r,$year=2557,$term=1,$status=0){
	
		$sql = " SELECT s.student_id,st.year,".$term." as term, s.code, st.class_id, st.room , cl.alian as class_name
				, CASE WHEN info.sex ='M' THEN 'ด.ช. ' ELSE 'ด.ญ. ' END AS title_name  
				, info.thai_name, info.thai_surname , ifnull(info.thai_nickname,'') as  thai_nickname
				, IFNULL( (SELECT sum(amount) sum FROM tb_student_payment_term pay1 
								 where pay1.student_id = s.student_id and pay1.year=".$year." 
								 and pay1.term=".$term." and pay1.fee_type=1),0) as fee_term_amt
								
				, IFNULL( (SELECT sum(pay_amount) FROM tb_student_payment_term pay1 
								 where pay1.student_id = s.student_id and pay1.year=".$year." 
								 and pay1.term=".$term." and pay1.fee_type=1),0) as fee_term_amt_pay				
								
				, IFNULL( (SELECT SUM(amount) FROM tb_student_payment_term pay1 
								  	 where pay1.student_id = s.student_id and pay1.year=".$year."  
									 and pay1.term=".$term." and pay1.fee_type <> 1),0) as fee_other_amt
				
				, IFNULL( (SELECT SUM(pay_amount) FROM tb_student_payment_term pay1 
									 where pay1.student_id = s.student_id and pay1.year=".$year."  
									 and pay1.term=".$term." and pay1.fee_type <> 1),0) as fee_other_amt_pay
				 FROM tb_student s 
				 LEFT JOIN tb_student_info info on info.student_id = s.student_id
				 JOIN tb_student_year st on st.student_id = s.student_id and st.year = ".$year." 
				 JOIN tb_class_level  cl on cl.id = st.class_id
				 WHERE 1=1 and s.delete_flag = 0 and s.status = 3 ";
		if ($c!=''){
			$sql .= " AND s.code ='".$c."'";
		}
		if ($sname!=''){
			$sname=trim($sname);
			list($n,$s)= explode(" ",$sname);
			$sql .=" AND (info.thai_name like '%".$n."%'";
			$sql .=" and info.thai_surname like '%".$s."%')";
		}
		if ($cid!=0){
			$sql .=" AND st.class_id =".$cid;
			if ($r!= -1){
				$sql.=" AND st.room =".$r;
			}
		}
		
		
		if ($status ==2){
			$sql = " SELECT * FROM ( " . $sql . " ) temp 	WHERE (fee_term_amt = fee_term_amt_pay and fee_other_amt = fee_other_amt_pay) ";
		}else if ($status==1){
			$sql = " SELECT * FROM ( " . $sql . " ) temp 	WHERE (fee_term_amt <> fee_term_amt_pay or fee_other_amt <> fee_other_amt_pay) ";
		}
		
		$sql .= " ORDER BY class_id,room, code  ";
			//print_r($sql);
		
	    $result = $this->ExecuteData($sql);
		
        $this->rowCount = $result->RecordCount();
        return $result;
	}
	function getAssetsPayment(){
		$sql = "SELECT  assets as id, assets as name
				FROM tb_master_fee
				WHERE effective_date  < now()
				ORDER BY effective_date asc " ;
				
		$result = $this->ExecuteData($sql);
		while($row = $result->FetchRow()){
			$ar[$row["name"]] = $row["id"];
		}
		return $ar;	
	}
	function getTermPayment(){
		$sql = " SELECT  term as id, term as name
				FROM tb_master_fee
				WHERE effective_date  < now()
				ORDER BY effective_date asc 
				";
		$result = $this->ExecuteData($sql);
		while($row = $result->FetchRow()){
			$ar[$row["name"]] = $row["id"];
		}
		return $ar;	
	
	}
	function updateOldStudent($id, $val){
		if(isset($val)){
			$sqlUpdate=$this->UpdateSqlScript(" SELECT  
													thai_name,	thai_surname ,	thai_nickname,
													eng_name,	eng_surname,	eng_nickname,
													sex,	religion,	race,	nationality ,
													birthdate,	address,	privince ,	zipcode ,
													telephone,	mobilephone ,	email ,
													emergency_contact,	no_of_brother1,	brother_1,	brother_2,	brother_3,
													no_of_brother2,	brother_4,	brother_5,	brother_6,
													blood ,	food_allergies,	be_allergic ,
													map,	congenital_disease ,	special_character ,
													parent_status ,	parent_status_remak ,
													father_name,	father_surname ,
													father_age,	father_education,
													father_position,	father_occupation,
													father_occupation_info ,	father_company ,
													father_company_address,	father_province,
													father_zipcode,	father_tele ,
													father_fax ,	mather_name,
													mather_surname,	mather_age ,
													mather_education,	mather_position,
													mather_occupation ,	mather_occupation_info,
													mather_company ,	mather_company_adress ,
													mather_province ,	mather_zipcode ,	mather_tele ,	mather_fax ,
													updatedate,	updateby  ,tele_parent_1,tele_parent_2,tele_parent_3
													FROM tb_student_info where student_id='".$id."'" ,$val);
													//print_r($sqlUpdate);
			 $this->ExecuteData($sqlUpdate);
			 
		}
	}
	function updateNewStudent($id, $val){
		if(isset($val)){
			$sqlUpdate=$this->UpdateSqlScript(" SELECT 
													thai_name,	thai_surname ,	thai_nickname,
													eng_name,	eng_surname,	eng_nickname,
													sex,	religion,	race,	nationality ,
													birthdate,	address,	privince ,	zipcode ,
													telephone,	mobilephone ,	email ,
													emergency_contact,	no_of_brother1,	brother_1,	brother_2,	brother_3,
													no_of_brother2,	brother_4,	brother_5,	brother_6,
													blood ,	food_allergies,	be_allergic ,
													map,	congenital_disease ,	special_character ,
													parent_status ,	parent_status_remak ,
													father_name,	father_surname ,
													father_age,	father_education,
													father_position,	father_occupation,
													father_occupation_info ,	father_company ,
													father_company_address,	father_province,
													father_zipcode,	father_tele ,
													father_fax ,	mather_name,
													mather_surname,	mather_age ,
													mather_education,	mather_position,
													mather_occupation ,	mather_occupation_info,
													mather_company ,	mather_company_adress ,
													mather_province ,	mather_zipcode ,	mather_tele ,	mather_fax ,
													free_assetment ,	free_term ,	free_other1,	free_other2 ,
													bus,	bus_payment ,	free_student_form ,
													init_class,	suggessted ,	doc_idcard,
													doc_address ,	doc_birth,	doc_pic_chident,
													doc_pic_parent ,	doc_healty ,
													updatedate,	updateby ,registerdate,tele_parent_1,tele_parent_2,tele_parent_3
													FROM tb_student_info where student_id='".$id."'" ,$val);
			 $this->ExecuteData($sqlUpdate);
			 
		}
	}
	function insertNewStudent($val){
		     if (isset($val)){
				$ar=array();
				$ar['createdate'] = $val['createdate'];
				$ar['createby'] = $val['createby'];
				$ar['status'] = 1;
				$ar['code'] = $this->getStudentRunning();
				
				$sql = $this->InsertSqlScript("SELECT status, code ,createdate,createby FROM tb_student WHERE 1=2",$ar);
				$this->ExecuteData($sql);
				$ID = $this->getLastID();
				
				$val['student_id']=  $ID;
				$sqlInsert=$this->InsertSqlScript(" SELECT student_id ,
													thai_name,	thai_surname ,	thai_nickname,
													eng_name,	eng_surname,	eng_nickname,
													sex,	religion,	race,	nationality ,
													birthdate,	address,	privince ,	zipcode ,
													telephone,	mobilephone ,	email ,
													emergency_contact,	no_of_brother1,	brother_1,	brother_2,	brother_3,
													no_of_brother2,	brother_4,	brother_5,	brother_6,
													blood ,	food_allergies,	be_allergic ,
													map,	congenital_disease ,	special_character ,
													parent_status ,	parent_status_remak ,
													father_name,	father_surname ,
													father_age,	father_education,
													father_position,	father_occupation,
													father_occupation_info ,	father_company ,
													father_company_address,	father_province,
													father_zipcode,	father_tele ,
													father_fax ,	mather_name,
													mather_surname,	mather_age ,
													mather_education,	mather_position,
													mather_occupation ,	mather_occupation_info,
													mather_company ,	mather_company_adress ,
													mather_province ,	mather_zipcode ,	mather_tele ,	mather_fax ,
													free_assetment ,	free_term ,	free_other1,	free_other2 ,
													bus,	bus_payment ,	free_student_form ,
													init_class,	suggessted ,	doc_idcard,
													doc_address ,	doc_birth,	doc_pic_chident,
													doc_pic_parent ,	doc_healty ,tele_parent_1,tele_parent_2,tele_parent_3,
													createdate,	createby,	updatedate,	updateby ,registerdate
													FROM tb_student_info
													where 1=2  ", $val);
				$this->ExecuteData($sqlInsert);
				
				return $ID;
            }
	}
	
    function getStudentRunning() {
        $_str = substr($this->getCurrentYear() . '01', -4);

        $sql = " SELECT running FROM tb_running WHERE type='STUDENT' and year ='" . $_str . "' ";
        $result = $this->ExecuteData($sql);

        $cnt = $result->RecordCount();

        if ($cnt == 0) {
            $sqlInser = "Insert Into tb_running (type, year, running) values ('STUDENT', '" . $_str . "', 1)";
            $this->ExecuteData($sqlInser);
            return $t . $_str . '0001';
        } else {
            $row = $result->FetchRow();
            $sqlupdate = "update tb_running set running=running+1 where type='STUDENT' and year ='" . $_str . "'";
            //print_r($sqlupdate);
            $this->ExecuteData($sqlupdate);
            $no = $row["running"] + 1;
            return $_str . str_repeat('0', 4 - strlen($no)) . $no;
        }
    }
	
	 function getSlipRunning() {
        $_str = substr($this->getCurrentYear() . '04', -4);

        $sql = " SELECT running FROM tb_running WHERE type='SLIP' and year ='" . $_str . "' ";
        $result = $this->ExecuteData($sql);

        $cnt = $result->RecordCount();

        if ($cnt == 0) {
            $sqlInser = "Insert Into tb_running (type, year, running) values ('SLIP', '" . $_str . "', 1)";
            $this->ExecuteData($sqlInser);
            return $t . $_str . '0001';
        } else {
            $row = $result->FetchRow();
            $sqlupdate = "update tb_running set running=running+1 where type='SLIP' and year ='" . $_str . "'";
            //print_r($sqlupdate);
            $this->ExecuteData($sqlupdate);
            $no = $row["running"] + 1;
            return $_str . str_repeat('0', 4 - strlen($no)) . $no;
        }
    }
	
	function AddPaymentTermFirstTime($student_id, $year, $student_term){
	 
			$sql = " INSERT INTO tb_student_payment_term(year,term , student_id, fee_type, amount) ";
			$sql .= " SELECT ".$year." as year,".$student_term." as term ,".$student_id." as s_id, 1 as fee_type, ifnull(free_term ,0) amt
					 FROM tb_student_info 
					 WHERE ifnull(free_term,0) <> 0 and student_id = ".$student_id ."
					 union all 

					 SELECT ".$year." as year , ".$student_term." as term ,".$student_id." as s_id, 2,  ifnull(free_assetment,0)
					 FROM tb_student_info 
					 WHERE ifnull(free_assetment,0) <> 0 and student_id = ".$student_id ."

					 union all 
					 SELECT ".$year." as year,".$student_term." as term ,".$student_id." as s_id, 3,ifnull(free_other1,0)
					 FROM tb_student_info 
					 WHERE ifnull(free_other1,0) <> 0 and student_id = ".$student_id ."

					 union all 
					 SELECT ".$year." as year,".$student_term." as term ,".$student_id." as s_id, 4, ifnull(free_other2,0) 
					 FROM tb_student_info
					 WHERE ifnull(free_other2,0) <> 0 and student_id = ".$student_id ."

					 union all 
					 SELECT ".$year." as year,".$student_term." as term ,".$student_id." as s_id, 5, ifnull(free_student_form,0)
					 FROM tb_student_info
					 WHERE ifnull(free_student_form,0) <> 0 and student_id = ".$student_id;
					
	    
			$this->ExecuteData($sql);
		 
	}
	function AddPaymentTerm($student_id, $year, $term){
	  
			$sql = " INSERT INTO tb_student_payment_term(year,term , student_id, fee_type, amount) ";
			$sql .= " SELECT ".$year." as year,".$term." as term ,".$student_id." as s_id, 1 as fee_type, ifnull(free_term ,0) amt
					 FROM tb_student_info
					 WHERE ifnull(free_term,0) <> 0 and student_id = ".$student_id ;
						
	   
			$this->ExecuteData($sql);
		 
	}
	function UpdateTerm($Year,$Term){
		$sql = " INSERT INTO tb_student_payment_term(year,term , student_id, fee_type, amount) ";
		$sql .= " SELECT ".$Year." as year,".$Term." as term , info.student_id as s_id, 1 as fee_type, ifnull(free_term ,0) amt ";
		$sql .=" FROM tb_student_info info
				 join tb_student s on s.student_id = info.student_id 
				 WHERE ifnull(free_term,0) <> 0 and s.status=3 ";
				print_r($sql);
		$this->ExecuteData($sql);
	}
	function getPaymentTerm($student_id, $year, $term){
	
		$sql = " SELECT sp.id, year, sp.term, t.term type_name , sp.amount 
				, (sp.amount - ifnull(sp.pay_amount,0) ) as remain
				, ifnulL(sp.pay_amount,0) pay_amount,sp.fee_type, month , 0 as type
				 From tb_student_payment_term sp 
				  join tb_fee_type t on t.id = sp.fee_type
				 WHERE isClose=0 and sp.isDelete=0  and sp.fee_type IN (1,2,3,4,10) and sp.year=".$year." and sp.term = ".$term." and student_id =" .$student_id;
		$sql .= " union all
				 SELECT sp.id, sp.year, sp.term, CONCAT(t.term , ' - ' , c.detail) type_name , sp.amount 
						, (sp.amount - ifnull(sp.pay_amount,0) ) as remain
						, ifnulL(sp.pay_amount,0) pay_amount,sp.fee_type , month , c.type
						 From tb_student_payment_term sp 
						  join tb_fee_type t on t.id = sp.fee_type
						 join tb_course c on c.id = sp.temp_id
						 WHERE isClose=0 and sp.isDelete=0 and sp.fee_type =6 and sp.year=".$year." and sp.term =".$term." and student_id =".$student_id."				 
				union all
				SELECT sp.id, sp.year, sp.term,CONCAT(t.term , ' - ' , c.detail) type_name , sp.amount 
						, (sp.amount - ifnull(sp.pay_amount,0) ) as remain
						, ifnulL(sp.pay_amount,0) pay_amount,sp.fee_type, month,  0
						 From tb_student_payment_term sp 
						  join tb_fee_type t on t.id = sp.fee_type
						 join tb_car_manage c on c.id = sp.temp_id
						 WHERE isClose=0 and sp.isDelete=0  and sp.fee_type =7 and sp.year=".$year." and sp.term =".$term." and student_id =".$student_id." ";
		 $result = $this->ExecuteData($sql);
		
        $this->rowCount = $result->RecordCount();
        return $result;
	}
	function getPaymentHistory($student_id){
	
		$sql = " SELECT  year, sp.term  , sum( sp.amount ) as amount, sp.receipt_code, DATE_FORMAT(sp.pay_date,'%d/%m/%Y') as paydate
				 , u.name as username, count(*) as cnt
				 From tb_student_payment_term sp 
				 JOIN tb_user u on u.id = sp.updateby
				 WHERE isClose=1 and   sp.isDelete=0  and student_id =" .$student_id ."
				 Group by sp.year, sp.term , sp.receipt_code, DATE_FORMAT(sp.pay_date,'%d/%m/%Y'), u.name 
				 Order by sp.year   desc , sp.term desc";
				 
		$result = $this->ExecuteData($sql);
		
        $this->rowCount = $result->RecordCount();
        return $result;
	}
	function getPaymentHistoryDetail($code){
		
		$sql = " SELECT  case when t.id=6 then c.detail else  t.term   end as type_name
				, sp.amount , sp.fee_type , c.type, sp.month
				 From tb_student_payment_term sp 
				 join tb_fee_type t on t.id = sp.fee_type
				 left join tb_course c on c.id = sp.temp_id
				 WHERE isClose=1 and   sp.isDelete=0 and  sp.receipt_code='".$code."' order by t.id asc";
				 
		 $result = $this->ExecuteData($sql);
		
        $this->rowCount = $result->RecordCount();
        return $result;
	
	}
	function UpdateSystemConfigYear($name, $value){
		$sql = "Update tb_system_config set value='".$value."' WHERE name='".$name."'";
		$this->ExecuteData($sql);
	}
	function delPaymentTerm($id){
		$sql = "UPDATE tb_student_payment_term set isDelete=1 WHERE id=".$id;
		$this->ExecuteData($sql);
	}
    function getRowCount(){
        
        return $this->rowCount;
        
    }	
    function insertData($val){
            if (isset($val)){
				$sqlInsert=$this->InsertSqlScript("SELECT name,status , createdate, createby FROM ". $this->table_name." where 1=2 ", $val);
				$this->ExecuteData($sqlInsert);
            }
    }
	function insertDataWithSql($sql , $val){
			if (isset($val)){
				$sqlInsert=$this->InsertSqlScript($sql, $val);
				//print_r($sqlInsert);
				$this->ExecuteData($sqlInsert);
            }
	}
	function PaymentUpdate($id,$val){
		if(isset($val)){
				$sqlUpdate=$this->UpdateSqlScript("SELECT receipt_code,pay_amount,isClose , pay_date, updateby FROM  tb_student_payment_term where id='".$id."'" ,$val);
				$this->ExecuteData($sqlUpdate);
				// return $sqlUpdate;
          }
	}
	 
	function PaymentUpdateAlll($id,$user_id,$code){
		$sql = " UPDATE tb_student_payment_term ";
		$sql .= " SET pay_amount = amount ,receipt_code='".$code."', isClose=1, pay_date=now(),updateby=".$user_id." WHERE student_id=".$id." and isClose=0 ";
		$this->ExecuteData($sql);
	}
    function updateData($id,$val){
            if(isset($val)){
				$sqlUpdate=$this->UpdateSqlScript("SELECT name,status , updatedate, updateby FROM ". $this->table_name." where id='".$id."'" ,$val);
				 $this->ExecuteData($sqlUpdate);
				// return $sqlUpdate;
            }
    }function updateDataWithSql( $sql , $val){
		  if(isset($val)){
				$sqlUpdate=$this->UpdateSqlScript($sql ,$val);
				 $this->ExecuteData($sqlUpdate);
				// return $sqlUpdate;
            }
	
	}
	function deleteStudent($id){
		$sql = "UPDATE tb_student set delete_flag = 1 WHERE student_id = ".$id;
		$this->ExecuteData($sql);
	}
    function deleData($id){
            if(isset($id)){
			$sqlDelete = "Delete FROM ". $this->table_name." where id='".$id."'";
			$this->ExecuteData($sqlDelete);
			return 1;
            }
    }
	function getType(){
	 
		$sqlscript= "select id, name from tb_product_type where status = 'Y' Order by id asc ";
		
		$result = $this->ExecuteData($sqlscript);
		while($row = $result->FetchRow()){
			$ar[$row["name"]] = $row["id"];
		}
		return $ar;	
	
	}
	function getTeacherClass_By_StudentID($id, $year){
		$sql = " SELECT ifnull(cr.teacher_1,'') as  teacher_1 , ifnull(cr.teacher_2,'') as teacher_2
				 , ifnull((SELECT course_fee FROM tb_employee where id = cr.teacher_1),0) as rate_1
				 , ifnull( (SELECT course_fee FROM tb_employee where id = cr.teacher_2),0) as rate_2
				 FROM tb_student_year  sy
				 JOIN tb_class_room  cr on cr.class_id = sy.class_id and cr.room = sy.room
				 WHERE student_id = ".$id."
				 AND year =".$year." 
				 ORDER BY sy.year desc
				 LIMIT 0, 1 " ;
				 
		$result = $this->ExecuteData($sql);
        $this->rowCount = $result->RecordCount();
        return $result->FetchRow();		 
	}
	function getCourseRate(){
		$sql = " SELECT * 
				FROM tb_course_fee ";
		
		$result = $this->ExecuteData($sql);
        $this->rowCount = $result->RecordCount();
        return $result->FetchRow();				
	
	}
	function getCurrentMonthSalary(){
		$sql="SELECT  DATE_FORMAT(value,'%Y/%m/%d') as CurrMonth
				FROM tb_system_config WHERE name = 'SALARY_MONTH'";
		$result = $this->ExecuteData($sql);
		$row=$result->FetchRow();
		return $row['CurrMonth'];
	}
	function checkCourseSalary($emp_id, $course_type,$rate, $user_id){
		$curr_month = $this->getCurrentMonthSalary();
		$IN_TYPE=$course_type;
		$sql = " SELECT emp.id, ifnull(s.emp_salary_id,0) as cnt
					FROM tb_employee_salary emp
					LEFT JOIN tb_salary_detail s on s.emp_salary_id = emp.id
						and s.`type` = 'A' and s.o_id =".$IN_TYPE."
					WHERE employee_id=".$emp_id. "
					and month = '".$curr_month."'";
		$result = $this->ExecuteData($sql);
		$row = $result->FetchRow();
		
		if($row['cnt'] > 0 ){
			//existing
			$sql = " UPDATE tb_salary_detail SET amount=amount+".$rate." WHERE type='A' and o_id=".$IN_TYPE." and emp_salary_id=".$row['id'];
			$this->ExecuteData($sql);
		}else{	
			//new 
			$sql=" INSERT INTO tb_salary_detail(emp_salary_id, o_id , amount ,type) ";
			$sql.= "values(".$row['id'].",".$IN_TYPE.", ".$rate." , 'A' )";
			$this->ExecuteData($sql);
		}
		 
		//update to main table salary
		$this->UpdateSalaryWithCourse($user_id,$rate,$emp_id,$curr_month);
	}
	function UpdateSalaryWithCourse($user_id, $pay, $emp_id,$month  ){
		$sql="UPDATE tb_employee_salary SET updatedate=now(), updateby=".$user_id .", other_income=other_income + ".$pay." WHERE employee_id=".$emp_id." and month='".$month."'";
		print_r($sql);
		$this->ExecuteData($sql);
	}
	function UpdatePayFullCourse($cid, $student_id,$year, $term,$user){
		$sql = " UPDATE tb_student_course SET is_pay=1,updatedate=now(), updateby=".$user." WHERE student_id=".$student_id." and course=".$cid." and year=".$year." and term=".$term;
		$this->ExecuteData($sql);
	}
	function UpdatePaymentCourse($no,$id,$amt,$user,$full=0){
		if( $full>0){
		$sql= "UPDATE tb_student_payment_term SET pay_amount=  amount
			, isClose= 1, receipt_code='".$no."' 
			, updateby=".$user ." ,pay_date=now() 
			WHERE id=".$id;
		}else{
			$sql= "UPDATE tb_student_payment_term SET pay_amount=". $amt ." 
			, isClose=0
			, updateby=".$user ." ,pay_date=now() 
			WHERE id=".$id;
		}
		$this->ExecuteData($sql);
	}
	function CourseNormal($cid){
		$sql= " SELECT  c.teacher, c.teacher2 ,c.cost cost
				 , ifnull((SELECT course_fee FROM tb_employee where id = c.teacher),0) rate_1
				, ifnull( (SELECT course_fee FROM tb_employee where id = c.teacher2),0) as rate_2
				FROM tb_course c 
				WHERE c.id=".$cid;
				
		$result = $this->ExecuteData($sql);
        $this->rowCount = $result->RecordCount();
        return $result->FetchRow();		
		
	}
	function CourseSwimimg($cid,$sid,$year, $term){
		$sql="SELECT  sc.teacher ,c.cost cost
			 FROM tb_course c 
			 JOIN tb_student_course sc on sc.course = c.id
			 WHERE c.course_type=3 
			 and sc.year=".$year." 
			 and sc.term =".$term." 
			 and sc.student_id=".$sid." 
			 and c.id=".$cid;
				
		$result = $this->ExecuteData($sql);
        $this->rowCount = $result->RecordCount();
        return $result->FetchRow();					 
	}
	function getCourseListByID($id){
		$sql = " SELECT  c.id , spt.student_id , c.course_type , c.detail as course_name, c.teacher, c.teacher2 , c.cost, t.name as cost_type , c.`type` as c_type
				,ifnull(sc.teacher,'') as teacher , spt.month
				 FROM tb_student_payment_term spt
				 JOIN tb_student_course sc on sc.year = spt.year 
						and sc.term = spt.term  and sc.student_id = spt.student_id
				 JOIN tb_course c on c.id = sc.course and c.id= spt.temp_id
				 LEFT JOIN tb_type_payment_service t on t.id = c.`type` 
				 WHERE spt.id=".$id." and fee_type=6 and spt.isDelete = 0 "; //and sc.is_pay = 0
				 
		$result = $this->ExecuteData($sql);
		
        $this->rowCount = $result->RecordCount();
        return $result;
	}
	function AddNewList($year,$term,$student_id, $fee_id , $amt){
		$sql = " INSERT INTO tb_student_payment_term (year,term, student_id,fee_type,amount) ";
		$sql.=" values(".$year.",".$term.",".$student_id.",". $fee_id.",".$amt.")";
		$result = $this->ExecuteData($sql);
	}
	function CheckList( $year, $term,$student_id, $fee_id){
		$sql = "select * ";
		$sql .= " from tb_student_payment_term ";
		$sql .= " where year=".$year." and term=".$term." and student_id=".$student_id." and isDelete=0 and fee_type=". $fee_id;
		
		$result = $this->ExecuteData($sql);
		if ($result->RecordCount() > 0){
			return 0;
		}else{
			return 1;
		}
    
	}
}
?>
